package DataBase;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;

import DataObjects.StandardMenuLeftList;

public class GetRecipes {

private String menuId;
	
	public GetRecipes(String mid)
	{
		this.menuId=mid;
	}

	
	
	
	public Vector<StandardMenuLeftList> getAllRecipes()
	{
		Vector<StandardMenuLeftList> menuRecipes=new Vector<StandardMenuLeftList>();
		
      SQLConnection sc=new SQLConnection();
		
		
		try {
			
			
			
		
		PreparedStatement ps = sc.getConnection().prepareStatement("SELECT res.Rid ,res.Rname,res.Rstatus,res.Rmishkal FROM recipe res WHERE res.Rid NOT in (SELECT rbm.Rid FROM  recipe_in_menu rbm WHERE rbm.Mid=?);");
        ps.setString(1,menuId);
		ResultSet rs=ps.executeQuery();	

		
		
		while(rs.next())
        {   

		
		 StandardMenuLeftList leftList = new StandardMenuLeftList();
      
        	 leftList.setRid(rs.getInt(1));
        	 leftList.setName(rs.getString(2));
        	 leftList.setStatus(rs.getString(3));
        	 leftList.setMishkal(rs.getFloat(4));
        	 	        	 
        	 menuRecipes.add(leftList);
         
        }//while
		    
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		//--------Calculate  price of recipe(uses last prices and quantity of items)
		try {
			
			PreparedStatement ps = sc.getConnection().prepareStatement("Select  SUM(price)" +
				                                                     	" FROM   " +
				                                                         	" (Select TRUNCATE(it.iCost*itr.iquantity,1) as price ,itr.iid,it.iCost,itr.iquantity " +
				                                                         	"     From item_in_recipe itr,item it        " +
				                                                             	"  Where itr.rid=? AND itr.iid=it.iid) AS cost");	
			
			PreparedStatement ps1 = sc.getConnection().prepareStatement("SELECT res.rconstPrice FROM recipe res Where res.rid=? ;");
			for(int i=0;i<menuRecipes.size();i++)
			{
				ps.setString(1, Integer.toString( menuRecipes.get(i).getRid()));
				ps1.setString(1, Integer.toString( menuRecipes.get(i).getRid()));
			 
			ResultSet rs=ps.executeQuery();	
			ResultSet rs1=ps1.executeQuery();	
			
			
			 while(rs.next() && rs1.next())
		        {   
                   int numTotal;
                   
                   menuRecipes.get(i).setCost(Float.parseFloat (rs.getString(1)));	       //--adding  price to details of recipe  
                   menuRecipes.get(i).setConstCost(Float.parseFloat(rs1.getString(1)));           //---adding constPrice(constant) of recipe 
				 
				 
				 
		         			        
		        }   //while
			    
				rs.close();
				rs1.close();
		        }   //for
			ps.close();
			ps1.close();
			sc.getConnection().close();
						
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
		
		
		
		return menuRecipes;
	}
}